
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[GetHotelData]
(	
	-- Add the parameters for the function here
)
RETURNS TABLE 
AS
RETURN 
(
	-- Add the SELECT statement with parameter references here
 SELECT 
	 h.ID as HotelMainID, h.HotelName,h.Prices, h.Images as HotelImage, h.[Description],h.Address1, h.Email,h.Phone1,
	 h.Fax,h.Phone2, h.Address2, h.Address3, ma.RoomCategoryId, ma.AvailableCount, ma.FromDate,
	 ma.ToDate, mr.RoomCategoryId as MaphotelRoomID, mr.PricePerHour, mr.PricePerDay, mr.[Description] as CateDes,
	 mu.Id, mu.Name as MoneyUnitName, cu.Country, cu.[Description] as countryDes, p.[Description] as provinceDesc, p.Ispopular,
	 re.ID as RegionID, re.RegionName, pr.PromotionName, pr.ID as PromotionID, pr.StartDate, pr.EndDate,  pr.[Description] as proDesc,
	 pr.FixedPricePerDay, pr.FixedPricePerHour, pr.DiscountPerHour, pr.DiscountPerDay, pr.Images,
	 ra.[Range],ra.[Description] as RangeDesc, se.ID as ServiceID, se.ServiceName, se.Images as serviceImage,
	 se.[Description] as ServiceDesc,co.Comment, co.Iexist, co.CreatedDate as commentCreateDate,
	 co.ModifiedDate as comentModifiedDate, co.Author, co.[Status],
	 bo.PaymentID, bo.CustomerName, bo.Phone, bo.MobilePhone, bo.CardNo, bo.TotalPaid, bo.[Address],bo.Email as customerEmail,bo.CheckInDate,
	 bo.CheckOutDate,bo.[Status] as BookingStatus, bo.CardHolder, bo.CardNumber, bo.HotelID as BookingHotelID,
	 bo.CreatedDate as bookingDate, bo.ModifiedDate as BookingModifiedDate, 
	 bo.NumberOfRooms as RoomTotalBooking, pm.PaymentMethod, pm.[Description] as paymentDesc,
	 ht.RoomCategoryId as RoomCateIDTerm, ht.Terms, ht.CreatedDate as TermCreatedDate,rc.Name as RoomCateName, rc.[Description] as RoomCateDesc
 FROM Hotels as h  
	 inner join MapHotelRoomAvailables as ma on h.ID = ma.HotelId
	 inner join MapHotelRoomCategories as mr on h.ID = mr.HotelId
	 left outer join HotelTerms as ht on h.ID = ht.HotelId
	 left outer join RoomCategories as rc on ht.RoomCategoryId = rc.ID
	 left outer join Bookings as bo on h.ID = bo.HotelId
	 left outer join MoneyUnits as mu on mr.MoneyUnitId = mu.ID
	 left outer join Provinces as p on h.ProvinceID = p.ID
	 left outer join Countries as cu on p.CountryID = cu.Id 
	 left outer join Regions as re on p.ID = re.ProvinceID 
	 left outer join Promotions as pr on h.ID = pr.HotelId
	 left outer join RangeHotel as ra on h.RangeID = re.ID
	 left outer join [Services] as se on h.ID = se.HotelId
	 left outer join Comments as co on h.ID = co.HotelId
	 left outer join PaymentMethod as pm on bo.PaymentID = pm.ID
)
GO
